<%@ page import="org.json.simple.*" %> <%--json_simple_1-1.jar--%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    import = "java.sql.*"
	%>
<%
	request.setCharacterEncoding("UTF-8");
	
	String url = "jdbc:oracle:thin:@155.230.193.42:1521:oraknu";
	String user = "system";
	String pass = "DBLab012345";
	Connection con = null;
	String sql = null;
	int result;
	JSONObject resultJson = new JSONObject();
	JSONArray resultArray = new JSONArray();

	final int maxMemoCount = 20;
	int pageNumber = 1;
	int category = 0;
	String title = "";
	String start_date = "2014/01/01";
	String end_date = "2015/12/31";

	String obj = request.getParameter("page");
	if( !(obj == null  || obj.trim().length() == 0))
		pageNumber = Integer.parseInt(obj);
	
	obj = request.getParameter("category");
	if( !(obj == null  || obj.trim().length() == 0))
		category = Integer.parseInt(obj);

	obj = request.getParameter("title");
	if( !(obj == null  || obj.trim().length() == 0))
		title = (String)obj;

	obj = request.getParameter("start_date");
	if( !(obj == null  || obj.trim().length() == 0))
		start_date = (String)obj;

	obj = request.getParameter("end_date");
	if( !(obj == null  || obj.trim().length() == 0))
		end_date = (String)obj;



	String id = (String) session.getAttribute("logID");
	try
	{
		Class.forName("oracle.jdbc.driver.OracleDriver");
		System.out.println("success");
	}
	catch(ClassNotFoundException e)
	{
		System.err.println("error = " + e.getMessage());
		System.exit(1);
	}
	try
	{
		con = DriverManager.getConnection(url, user, pass);
	}
	catch(SQLException e)
	{
		System.err.println("sql error = " + e.getMessage());
		System.exit(1);
	}
	try
	{
		con.setAutoCommit(false);
		
		
		PreparedStatement pstmt = null;
		if(!title.equals(""))
		{
			if(category == 0)
			{
				sql = "select * from ";
					sql += "(select a.*, rownum as rnum, to_char(pubdate, 'yyyy:mm:dd hh24:mi:ss') as pdate from ";
						sql += "(select * from article ";
						 sql += "where title like '%'||?||'%' and pubdate >= to_date(?||' 00:00:00', 'yyyy/mm/dd hh24:mi:ss') and pubdate <= to_date(?||' 23:59:59', 'yyyy/mm/dd hh24:mi:ss') order by aid desc ";
						sql += ") a";
					sql += ") where rnum > ? and rnum <= ?";
				pstmt = con.prepareStatement(sql);

				pstmt.setString(1, title);
				pstmt.setString(2, start_date);
				pstmt.setString(3, end_date);
				pstmt.setInt(4, (pageNumber - 1) * maxMemoCount);
				pstmt.setInt(5, (pageNumber) * maxMemoCount);
				
			}
			else
			{	
				sql = "select * from ";
					sql += "(select a.*, rownum as rnum, to_char(pubdate, 'yyyy:mm:dd hh24:mi:ss') as pdate from ";
						sql += "(select * from article ";
						 sql += "where  ID_C = ? and title like '%'||?||'%' and pubdate >= to_date(?||' 00:00:00', 'yyyy/mm/dd hh24:mi:ss') and pubdate <= to_date(?||' 23:59:59', 'yyyy/mm/dd hh24:mi:ss') order by aid desc ";
						sql += ") a";
					sql += ") where rnum > ? and rnum <= ?";
				pstmt = con.prepareStatement(sql);

				pstmt.setInt(1, category);
				pstmt.setString(2, title);
				pstmt.setString(3, start_date);
				pstmt.setString(4, end_date);
				pstmt.setInt(5, (pageNumber - 1) * maxMemoCount);
				pstmt.setInt(6, (pageNumber) * maxMemoCount);
				
			}
		}
		else if(category == 0)
		{
				sql = "select * from ";
					sql += "(select a.*, rownum as rnum, to_char(pubdate, 'yyyy:mm:dd hh24:mi:ss') as pdate from ";
						sql += "(select * from article ";
						 sql += "where pubdate >= to_date(?||' 00:00:00', 'yyyy/mm/dd hh24:mi:ss') and pubdate <= to_date(?||' 23:59:59', 'yyyy/mm/dd hh24:mi:ss') order by aid desc ";
						sql += ") a";
					sql += ") where rnum > ? and rnum <= ?";
			pstmt = con.prepareStatement(sql);
			//pstmt.setString(1, id);

			pstmt.setString(1, start_date);
			pstmt.setString(2, end_date);
			pstmt.setInt(3, (pageNumber - 1) * maxMemoCount);
			pstmt.setInt(4, (pageNumber) * maxMemoCount);

			
		}
		else
		{
				sql = "select * from ";
					sql += "(select a.*, rownum as rnum, to_char(pubdate, 'yyyy:mm:dd hh24:mi:ss') as pdate from ";
						sql += "(select * from article ";
						 sql += "where  ID_C = ? and pubdate >= to_date(?||' 00:00:00', 'yyyy/mm/dd hh24:mi:ss') and pubdate <= to_date(?||' 23:59:59', 'yyyy/mm/dd hh24:mi:ss') order by aid desc ";
						sql += ") a";
					sql += ") where rnum > ? and rnum <= ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, category);
			pstmt.setString(2, start_date);
			pstmt.setString(3, end_date);
			pstmt.setInt(4, (pageNumber - 1) * maxMemoCount);
			pstmt.setInt(5, (pageNumber) * maxMemoCount);
			
		}
		

		ResultSet rs = pstmt.executeQuery();
		while(rs.next())
		{
			String cont = rs.getString("contents").replaceAll("<(/)?([a-zA-Z]*)(\\s[a-zA-Z]*=[^>]*)?(\\s)*(/)?>", "").replaceAll("\\n", "");;
			if(cont.length() > 100)
				cont = cont.substring(0, 100);

			String articleTitle = rs.getString("title").replaceAll("<(/)?([a-zA-Z]*)(\\s[a-zA-Z]*=[^>]*)?(\\s)*(/)?>", "");
			
			resultJson = new JSONObject();
			resultJson.put("aid", rs.getInt("aid"));
			resultJson.put("pubtime", rs.getString("pdate"));
			resultJson.put("cont", cont.toString());
			resultJson.put("title", articleTitle.toString());
			resultJson.put("thumb", rs.getString("THUMB"));
			resultArray.add(resultJson);
		}
		pstmt.close();
		con.close();
		
		out.print(resultArray);
	}
	catch(Exception e)
	{
		System.err.println("sql error = " + e.getMessage());
	}
			
%>